package org.firefly.db.dialect;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.firefly.db.meta.ColumnMeta;
import org.firefly.db.meta.TableMeta;

/**
 * SQL Server2005 数据库方言
 * @author firefly
 *
 */
public class MSSQL2005Dialect extends DialectImpl {
	private static Map<Integer,String> types = new HashMap<Integer,String>();
	
	static{
		types.put(Types.CHAR, "char");
		types.put(Types.VARCHAR, "varchar");
		types.put(Types.NUMERIC, "numeric");
		types.put(Types.DECIMAL, "decimal");
		types.put(Types.INTEGER, "int");
		types.put(Types.BIGINT, "bigint");
		types.put(Types.FLOAT, "float");
		types.put(Types.DOUBLE, "float");
		types.put(Types.BIT, "bit");
		types.put(Types.TINYINT, "tinyint");
		types.put(Types.SMALLINT, "smallint");
		types.put(Types.NVARCHAR, "nvarchar");
		types.put(Types.CLOB, "text");
		types.put(Types.LONGVARCHAR, "text");
		types.put(Types.BLOB, "image");
		types.put(Types.VARBINARY, "varbinary");
		types.put(Types.DATE, "date");
		types.put(Types.TIMESTAMP, "datetime");
		types.put(Types.BINARY, "timestamp");
		types.put(Types.LONGVARBINARY, "image");
	}

	
	@Override
	public String processSQLVal(Object val,int javaType, ColumnMeta cm){
		if(val==null)return "NULL";
		try {
			switch(javaType){
				case Types.CHAR: 
				case Types.CLOB:
				case Types.NVARCHAR:
				case Types.VARCHAR: return processStr(String.valueOf(val));
				case Types.NUMERIC: 
				case Types.DECIMAL: 
					BigDecimal bd = null;
					if(val instanceof BigDecimal){
						bd= BigDecimal.class.cast(val);
					}else if(val instanceof String){
						bd = new BigDecimal((String)val);
					}
					return bd==null?"NULL":bd.toString();
				case Types.INTEGER:
				case Types.TINYINT:
				case Types.SMALLINT:
					return Integer.class.cast(val).toString();
				case Types.BIT:
					return Boolean.class.cast(val)?"1":"0";
				case Types.BIGINT: return Long.class.cast(val).toString();
				case Types.FLOAT: return Float.class.cast(val).toString();
				case Types.DOUBLE: return Double.class.cast(val).toString();
				case Types.DATE: 
					if(String.class.isInstance(val)){
						return "'"+val.toString()+"'";
					}else if(Date.class.isInstance(val)){
						return "'"+dateSDF.format((Date)val)+"'";
					}
				case Types.TIMESTAMP:
					if(String.class.isInstance(val)){
						return "'"+val.toString()+"'";
					}else if(Date.class.isInstance(val)){
						return "'"+dateTimeSDF.format((Date)val)+"'";
					}
				case Types.VARBINARY:
				case Types.BLOB:
				default:return "NULL";
			}
		} catch (ClassCastException e) {
			e.printStackTrace();
		}
		return "NULL";
	}

	@Override
	public boolean columnTypeHasDecimal(int javaType){
		switch(javaType){
			case Types.DECIMAL: 
			case Types.NUMERIC: return true;
			default:return false;
		}
	}
	
	@Override
	public boolean columnTypeHasLength(int javaType){
	    switch(javaType){
            case Types.CHAR: 
            case Types.VARCHAR: 
            case Types.NVARCHAR: 
            case Types.DECIMAL:
            case Types.VARBINARY:
            case Types.NUMERIC: return true;
            
            case Types.INTEGER: 
            case Types.CLOB:
            case Types.DATE: 
            case Types.TIMESTAMP:
            case Types.BLOB:
            default:return false;
        }
	}
	
	@Override
	public boolean columnTypeHasLength(ColumnMeta cm,int javaType){
		return columnTypeHasLength(javaType);
	}
	
	@Override
	protected Map<Integer,String> getTypes() {
		return types;
	}
	
	@Override
	public String getNamingChar() {
		return "\"";
	}
	
	@Override
	public String getConcatStr(String a,String b){
		return a+"+"+b;
	}
	
	//为clob列提供多次update进行数据插入。
	private static final String COLB_INSERT_SQL="DECLARE [varName] binary(16) SELECT [varName] = TEXTPTR([colbName]) from [tablename] where [pkName]=[pkVal] UPDATETEXT [tablename].[colbName] [varName] null 0 [colPartVal]";
	public List<String> buildClobSql(TableMeta targetTM, String columnName, Map<String,Object> val) throws SQLException {
		List<String> colbSQLs = new ArrayList<String>(); 
		String colVal = String.valueOf(val.get(columnName));
		String pkName = targetTM.getPKname();
		Object pkVal = val.get(pkName);
		if(pkName==null || "".equals(pkName.trim())){
			throw new SQLException("没有为表["+targetTM.getName()+"]提供主键列！请补充后再进行数据处理！");
		}
		String colbSQL = COLB_INSERT_SQL.replace("[tablename]", targetTM.getName()).replace("[colbName]", columnName)
					.replace("[pkName]", pkName).replace("[pkVal]", processStr(pkVal.toString()));
		int m = 500;
		for (int k = 0; k < colVal.length() ; k+=m) {  
			int len = colVal.length()-k;
			String partVal = colVal.substring(k, k+(len>m?m:len));
			colbSQLs.add(colbSQL.replace("[colPartVal]", processStr(partVal)).replace("[varName]", "@ptrval_" + k 
			        + "_" + Math.round(Math.random()*10000000) + "_" + columnName));
		}
		return colbSQLs;
	}

	private static final String PAGINATION_SQL="SELECT [selectSql] FROM [tablename] [tAlias}"
			+ ",(SELECT TOP [curPageNo*pageSize] row_number() OVER ([orderBySql]) n, [tAlias].[pk] [fromTableSql] [whereSql]) pag_"
			+ " WHERE [tAlias].[pk] = pag_.[pk] AND pag_.n > [(curPageNo-1)*pageSize] ORDER BY pag_.n ASC";
	@Override
	public String getPaginationSql(String selectSql,
			String tablename,
			String tAlias,
			String pk,
			String fromTableSql,
			String orderBySql,String whereSql, int curPageNo, int pageSize) {
		String pagSQL = PAGINATION_SQL.replace("[selectSql]", selectSql).replace("[tablename]", tablename)
				.replace("[tAlias]", tAlias).replace("[pk]", pk)
				.replace("[fromTableSql]", fromTableSql).replace("[orderBySql]",orderBySql)
				.replace("[whereSql]", whereSql).replace("[curPageNo*pageSize]", String.valueOf((curPageNo*pageSize)))
				.replace("[(curPageNo-1)*pageSize]", String.valueOf((curPageNo-1)*pageSize));
		return pagSQL;
	}

	@Override
	public String getModifyColumnKey() {
		return "alter column";
	}
}
